package com.airline.connection;



import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.airline.beans.Customer;
import com.airline.beans.Employee;
import com.airline.beans.Flight;
import com.airline.beans.Journey;
import com.airline.beans.Person;


public class DatabaseConnection {
	
	DataSource dataSource;
	Connection con = null;
	ResultSet rs;
	ResultSet rs2;
    Statement stmt = null;
private ConnectionPool cp;  
    
	DatabaseConnection(){		
		try {	
			if (con == null) {
				
				
				System.out.println("Connection established");
			     
			     cp = ConnectionPool.getInstance();
				 con = cp.checkout();
			}
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
    
//	DatabaseConnection(){

//		try {	
//			if (con == null) {
//				
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
//				con = DriverManager.getConnection("jdbc:mysql://localhost:3306/airlinesystem","root","");
//				stmt = con.createStatement();
//			}
//			
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			} catch (InstantiationException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			} catch (IllegalAccessException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			} catch (ClassNotFoundException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//	}
	
		
	public Customer getCustomerByID(int customerID)
	{
		PreparedStatement searchStatement = null;
		Customer tempCustomer = null;
		try {
			String query = "Select person.personID, person.password, person.firstName, person.lastName, " +
					"person.address, person.city, person.state, person.zipCode, person.dateOfBirth, customer.passportNumber, " +
					"customer.nationality from person, customer " +
					"where personID = ? and customerID = ?";
	        searchStatement = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	        searchStatement.setInt(1, customerID);
	        searchStatement.setInt(2, customerID);
			rs = searchStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempCustomer = new Customer();
				tempCustomer.setPersonID(convertIDToString(rs.getInt("person.personID")));
				tempCustomer.setPassword(rs.getString("person.password"));
				tempCustomer.setFirstName(rs.getString("person.firstName"));
				tempCustomer.setLastName(rs.getString("person.lastName"));
				tempCustomer.setAddress(rs.getString("person.address"));
				tempCustomer.setCity(rs.getString("person.city"));
				tempCustomer.setState(rs.getString("person.state"));
				tempCustomer.setZipCode(rs.getString("person.zipCode"));
				tempCustomer.setDateOfBirth(rs.getString("person.dateOfBirth"));
				tempCustomer.setPassportNumber(rs.getString("customer.passportNumber"));
				tempCustomer.setNationality(rs.getString("customer.nationality"));
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=searchStatement)searchStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return tempCustomer;
	}

	//No longer used when connection pooling went in
//	private Person getPersonByID(int personID)
//	{
//		Person tempPerson = null;
//		try {
//			con = dataSource.getConnection();
//
//			stmt = con.createStatement();
//	        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//			String query = "Select * from person where personID = " + personID;
//			rs = stmt.executeQuery(query);
//			if(rs.next() != false) {
//				//fill in the person object
//				tempPerson = new Person();
//				tempPerson.setPersonID(convertIDToString(rs.getInt("personID")));
//				tempPerson.setPassword(rs.getString("password"));
//				tempPerson.setFirstName(rs.getString("firstName"));
//				tempPerson.setLastName(rs.getString("lastName"));
//				tempPerson.setAddress(rs.getString("address"));
//				tempPerson.setCity(rs.getString("city"));
//				tempPerson.setState(rs.getString("state"));
//				tempPerson.setZipCode(rs.getString("zipCode"));
//				tempPerson.setDateOfBirth(rs.getString("dateOfBirth"));
//			}
//		} catch (SQLException e) {
//			//TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//        try { if(null!=rs)rs.close();} catch (SQLException e)
//        {e.printStackTrace();}
//        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//        {e.printStackTrace();}
//        try { if(null!=con)con.close();} catch (SQLException e)
//        {e.printStackTrace();}
//		}
//		return tempPerson;
//	}
//	
//	private Customer getCustomerDataByID(int customerID)
//	{
//		Customer tempCustomer = null;
//		try {
//			con = dataSource.getConnection();
//
//			stmt = con.createStatement();
//	        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//			String query = "Select * from customer where customerID = " + customerID;
//			rs = stmt.executeQuery(query);
//			if(rs.next() != false) {
//				//fill in the person object
//				tempCustomer = new Customer();
//				tempCustomer.setPersonID(convertIDToString(rs.getInt("customerID")));
//				tempCustomer.setPassportNumber(rs.getString("passportNumber"));
//				tempCustomer.setNationality(rs.getString("nationality"));
//			}
//		} catch (SQLException e) {
//			//TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//	        try { if(null!=rs)rs.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=con)con.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//		}
//		return tempCustomer;
//	}
//	
//	private Employee getEmployeeDataByID(int employeeID)
//	{
//		Employee tempEmployee = null;
//		try {
//			con = dataSource.getConnection();
//
//			stmt = con.createStatement();
//	        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//			String query = "Select * from employee where employeeID = " + employeeID;
//			rs = stmt.executeQuery(query);
//			if(rs.next() != false) {
//				//fill in the person object
//				tempEmployee = new Employee();
//				tempEmployee.setPersonID(convertIDToString(rs.getInt("employeeID")));
//				tempEmployee.setWorkDescription(rs.getString("workDescription"));
//				tempEmployee.setPosition(rs.getString("position"));
//				tempEmployee.setHireDate(rs.getString("hireDate"));
//			}
//		} catch (SQLException e) {
//			//TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//	        try { if(null!=rs)rs.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=con)con.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//			}
//		return tempEmployee;
//	}
	
	public Employee getEmployeeByID(int employeeID)
	{
		PreparedStatement searchStatement = null;
		Employee tempEmployee = null;
		try {
			String query = "Select person.personID, person.password, person.firstName, person.lastName, " +
					"person.address, person.city, person.state, person.zipCode, person.dateOfBirth, employee.workDescription, " +
					"employee.position, employee.hireDate from person, employee " +
					"where personID = ? and employeeID = ?";
	        searchStatement = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			searchStatement.setInt(1, employeeID);
			searchStatement.setInt(2, employeeID);
	        rs = searchStatement.executeQuery();

			if(rs.next() != false) {
				//fill in the person object
				tempEmployee = new Employee();
				tempEmployee.setPersonID(convertIDToString(rs.getInt("person.personID")));
				tempEmployee.setPassword(rs.getString("person.password"));
				tempEmployee.setFirstName(rs.getString("person.firstName"));
				tempEmployee.setLastName(rs.getString("person.lastName"));
				tempEmployee.setAddress(rs.getString("person.address"));
				tempEmployee.setCity(rs.getString("person.city"));
				tempEmployee.setState(rs.getString("person.state"));
				tempEmployee.setZipCode(rs.getString("person.zipCode"));
				tempEmployee.setDateOfBirth(rs.getString("person.dateOfBirth"));
				tempEmployee.setWorkDescription(rs.getString("employee.workDescription"));
				tempEmployee.setPosition(rs.getString("employee.position"));
				tempEmployee.setHireDate(rs.getString("employee.hireDate"));
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        //for connection pooling
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        //for preparedstatements
	        try { if(null!=searchStatement)searchStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return tempEmployee;
	}
	
	public String createEmployee(Employee employee) 
	{
		int createResult = 0;
		int personID = 0;
		
		//first insert into the Person table, getting back a personID
		personID = insertPerson(employee);
		//if person insert successful, continue to add other information to the employee table
		//else, return database fault
		if (personID > 0) {
			employee.setPersonID(convertIDToString(personID));
			createResult = insertEmployee(employee);
			if (createResult == 1) {
				return convertIDToString(personID);
			}
		} 	
		return convertIDToString(personID);
	}
	
	private int insertEmployee(Employee employee) 
	{	
		PreparedStatement insertStatement = null;
		int result = 0;
		int rowcount;
		try {
			String query = "Insert into employee (employeeID, workDescription, position, hireDate)" +
					"values (?, ?, ?, ?)";
			insertStatement = con.prepareStatement(query);
			insertStatement.setInt(1, convertIDToInteger(employee.getPersonID()));
			insertStatement.setString(2, employee.getWorkDescription());
			insertStatement.setString(3, employee.getPosition());
			insertStatement.setString(4, employee.getHireDate());

			rowcount=insertStatement.executeUpdate();
			if(rowcount > 0){
				result = 1;
			}
			else{
				result=0;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=insertStatement)insertStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public String createCustomer(Customer customer) 
	{
		int createResult = 0;
		int personID = 0;
		
		//first insert into the Person table, getting back a personID
		personID = insertPerson(customer);
		//if person insert successful, continue to add other information to the customer table
		//else, return database fault
		if (personID > 0) {
			customer.setPersonID(Integer.toString(personID));
			createResult = insertCustomer(customer);
			if (createResult == 1) {
				return convertIDToString(personID);
			}
		} 	
		return convertIDToString(personID);
	}
	
	private int insertCustomer(Customer customer) 
	{
		PreparedStatement insertStatement = null;
		int result = 0;
		int rowcount;
		try {
			String query = "Insert into customer (customerID, passportNumber, nationality) values ("
					+ "?, ?, ?, ?)";
			insertStatement = con.prepareStatement(query);
			insertStatement.setInt(1, convertIDToInteger(customer.getPersonID()));
			insertStatement.setString(2, customer.getPassportNumber());
			insertStatement.setString(3, customer.getNationality());

			rowcount=insertStatement.executeUpdate();
			if(rowcount > 0){
				result = 1;
			}
			else{
				result = 0;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=insertStatement)insertStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	private int insertPerson(Person person) 
	{	
		PreparedStatement insertStatement = null;
		PreparedStatement idQueryStatement = null;
		int result = 0;
		int rowcount;
		try {
			String query = "Insert into person (password, firstName, lastName, address, city, state, zipCode, dateOfBirth) values (" 
					+ "?, ?, ?, ?, ?, ?, ?, ?)"; 
			insertStatement = con.prepareStatement(query);
			insertStatement.setString(1, person.getPassword());
			insertStatement.setString(2, person.getFirstName());
			insertStatement.setString(3, person.getLastName());
			insertStatement.setString(4, person.getAddress());
			insertStatement.setString(5, person.getCity());
			insertStatement.setString(6, person.getState());
			insertStatement.setString(7, person.getZipCode());
			insertStatement.setString(8, person.getDateOfBirth());
			rowcount=insertStatement.executeUpdate();
			if(rowcount > 0){
				idQueryStatement = con.prepareStatement("Select last_insert_id()");
				rs = idQueryStatement.executeQuery();
//				String idQuery = "Select last_insert_id()";
//				rs=stmt.executeQuery(idQuery);
				if(rs.next() != false){
					result = rs.getInt(1);
				}
			}
			else{
				result=0;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=insertStatement)insertStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=idQueryStatement)idQueryStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	//Combining for preparedStatements
//	private Boolean deletePerson(int personID)
//	{
//		Boolean result = false;
//		int rowcount;
//		try {
//			con = dataSource.getConnection();
//			stmt = con.createStatement(); 
//			
//			String query = "Delete from person where personID = " + personID;
//			rowcount=stmt.executeUpdate(query);
//			if(rowcount > 0){
//				return true;
//			}		
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//	        try { if(null!=rs)rs.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=con)con.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//			}
//		return result;
//	}
//	
//	public Boolean deleteEmployee(int employeeID)
//	{
//		Boolean result = false;
//		int rowcount;
//		try {
//			con = dataSource.getConnection();
//			stmt = con.createStatement();
//			
//			//First, delete the person's information from the person table
//			result = deletePerson(employeeID);
//			if (result == true) {
//				String query = "Delete from employee where employeeID = " + employeeID;
//				rowcount=stmt.executeUpdate(query);
//				if(rowcount > 0){
//					return true;
//				}
//			}	
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//	        try { if(null!=rs)rs.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=con)con.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//			}
//		return result;
//		
//	}
	
	public Boolean deleteEmployee(int personID)
	{
		PreparedStatement deletePersonStatement = null;
		PreparedStatement deleteEmployeeStatement = null;
		Boolean result = false;
		try {
			
			String deletePersonQuery = "Delete from person where personID = ?";
			String deleteEmployeeQuery = "Delete from employee where employeeID = ?";
			
			deletePersonStatement = con.prepareStatement(deletePersonQuery);
			deletePersonStatement.setInt(1, personID);
			deletePersonStatement.executeUpdate();
			deleteEmployeeStatement = con.prepareStatement(deleteEmployeeQuery);
			deleteEmployeeStatement.setInt(1, personID);
			deleteEmployeeStatement.executeUpdate();
			result = true;
			
	    } catch (SQLException e ) {
	        e.printStackTrace();
	        if (con != null) {
	            try {
	                System.err.print("Transaction is being rolled back");
	                con.rollback();
	                result = false;
	            } catch(SQLException excep) {
	                excep.printStackTrace();
	            }
	        }
	    } finally {
	        if (deletePersonStatement != null) {
	            try {
					deletePersonStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	        }
	        if (deleteEmployeeStatement != null) {
	            try {
					deleteEmployeeStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	        }
	        cp.checkin(con);
	    }
		return result;
	}
	
	public Boolean updateEmployee(Employee employee) 
	{
		PreparedStatement updatePersonStatement = null;
		PreparedStatement updateEmployeeStatement = null;
		Boolean result = false;
		int employeeID = convertIDToInteger(employee.getPersonID());
		Person tempPerson = null;
		Employee tempEmployee = null;
		
		try {

			String updatePersonQuery = "Select * from person where personID = ?";
	        updatePersonStatement = con.prepareStatement(updatePersonQuery, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			updatePersonStatement.setInt(1, employeeID);
	        rs = updatePersonStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempPerson = new Person();
				tempPerson.setPersonID(convertIDToString(rs.getInt("personID")));
				tempPerson.setPassword(rs.getString("password"));
				tempPerson.setFirstName(rs.getString("firstName"));
				tempPerson.setLastName(rs.getString("lastName"));
				tempPerson.setAddress(rs.getString("address"));
				tempPerson.setCity(rs.getString("city"));
				tempPerson.setState(rs.getString("state"));
				tempPerson.setZipCode(rs.getString("zipCode"));
				tempPerson.setDateOfBirth(rs.getString("dateOfBirth"));
			}
			//update the person data first
			//TODO: need to do the checks if the data changed before it will be updated, right now just does all
//			Person tempPerson = getPersonByID(convertIDToInteger(employee.getPersonID()));			
			if (tempPerson != null && rs.getInt("personID") == employeeID) {
				rs.updateString("password", employee.getPassword());
				rs.updateString("firstName", employee.getFirstName());
				rs.updateString("lastName", employee.getLastName());
				rs.updateString("address", employee.getAddress());
				rs.updateString("city", employee.getCity());
				rs.updateString("state", employee.getState());
				rs.updateString("zipCode", employee.getZipCode());
				rs.updateString("dateOfBirth", employee.getDateOfBirth());
				rs.updateRow();
			}
			
//			query = "Select * from employee where employeeID = " + employeeID;
//			rs = stmt.executeQuery(query);
			String updateEmployeeQuery = "Select * from employee where employeeID = ?";
			updateEmployeeStatement = con.prepareStatement(updateEmployeeQuery, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			updateEmployeeStatement.setInt(1, employeeID);
			rs = updateEmployeeStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempEmployee = new Employee();
				tempEmployee.setPersonID(convertIDToString(rs.getInt("employeeID")));
				tempEmployee.setWorkDescription(rs.getString("workDescription"));
				tempEmployee.setPosition(rs.getString("position"));
				tempEmployee.setHireDate(rs.getString("hireDate"));
			}
			//update the employee data second
//			Employee tempEmployee = getEmployeeDataByID(convertIDToInteger(employee.getPersonID()));
			if (tempEmployee != null && rs.getInt("employeeID") == employeeID) {
				rs.updateString("workDescription", employee.getWorkDescription());
				rs.updateString("position", employee.getPosition());
				rs.updateString("hireDate", employee.getHireDate());
				rs.updateRow();
				result = true;
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=updatePersonStatement)updatePersonStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=updateEmployeeStatement)updateEmployeeStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public Boolean deleteCustomer(int personID)
	{
		PreparedStatement deletePersonStatement = null;
		PreparedStatement deleteCustomerStatement = null;
		Boolean result = false;
		try {
			con = dataSource.getConnection();
			
			String deletePersonQuery = "Delete from person where personID = ?";
			String deleteCustomerQuery = "Delete from employee where customerID = ?";
			
			con.setAutoCommit(false);
			deletePersonStatement = con.prepareStatement(deletePersonQuery);
			deletePersonStatement.setInt(1, personID);
			deletePersonStatement.executeUpdate();
			deleteCustomerStatement = con.prepareStatement(deleteCustomerQuery);
			deleteCustomerStatement.setInt(1, personID);
			deleteCustomerStatement.executeUpdate();
			con.commit();
			result = true;
			
	    } catch (SQLException e ) {
	        e.printStackTrace();
	        if (con != null) {
	            try {
	                System.err.print("Transaction is being rolled back");
	                con.rollback();
	                result = false;
	            } catch(SQLException excep) {
	                excep.printStackTrace();
	            }
	        }
	    } finally {
	        if (deletePersonStatement != null) {
	            try {
					deletePersonStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	        }
	        if (deleteCustomerStatement != null) {
	            try {
					deleteCustomerStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	        }
	        try {
				con.setAutoCommit(true);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	    }
		return result;
	}
	
	//Combined with deletePerson for preparedStatements.
//	public Boolean deleteCustomer(int customerID)
//	{
//		Boolean result = false;
//		int rowcount;
//		try {
//			con = dataSource.getConnection();
//			stmt = con.createStatement();
//			//First, delete the person's information from the person table
//			result = deletePerson(customerID);
//			if (result == true) {
//				String query = "Delete from customer where customerID = " + customerID;
//				rowcount=stmt.executeUpdate(query);
//				if(rowcount > 0){
//					return true;
//				}
//			}	
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//	        try { if(null!=rs)rs.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//	        try { if(null!=con)con.close();} catch (SQLException e)
//	        {e.printStackTrace();}
//			}
//		return result;
//	}
	
	public Boolean updateCustomer(Customer customer) 
	{
		PreparedStatement updatePersonStatement = null;
		PreparedStatement updateCustomerStatement = null;
		Boolean result = false;
		int customerID = convertIDToInteger(customer.getPersonID());
		Person tempPerson = null;
		Customer tempCustomer = null;
		
		try {

			String updatePersonQuery = "Select * from person where personID = ?";
	        updatePersonStatement = con.prepareStatement(updatePersonQuery, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			updatePersonStatement.setInt(1, customerID);
	        rs = updatePersonStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempPerson = new Person();
				tempPerson.setPersonID(convertIDToString(rs.getInt("personID")));
				tempPerson.setPassword(rs.getString("password"));
				tempPerson.setFirstName(rs.getString("firstName"));
				tempPerson.setLastName(rs.getString("lastName"));
				tempPerson.setAddress(rs.getString("address"));
				tempPerson.setCity(rs.getString("city"));
				tempPerson.setState(rs.getString("state"));
				tempPerson.setZipCode(rs.getString("zipCode"));
				tempPerson.setDateOfBirth(rs.getString("dateOfBirth"));
			}
			
			if (tempPerson != null && rs.getInt("personID") == customerID) {
				rs.updateString("password", customer.getPassword());
				rs.updateString("firstName", customer.getFirstName());
				rs.updateString("lastName", customer.getLastName());
				rs.updateString("address", customer.getAddress());
				rs.updateString("city", customer.getCity());
				rs.updateString("state", customer.getState());
				rs.updateString("zipCode", customer.getZipCode());
				rs.updateString("dateOfBirth", customer.getDateOfBirth());
				rs.updateRow();
			}
			
//			query = "Select * from employee where employeeID = " + employeeID;
//			rs = stmt.executeQuery(query);
			String updateCustomerQuery = "Select * from customer where customerID = ?";
			updateCustomerStatement = con.prepareStatement(updateCustomerQuery, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			updateCustomerStatement.setInt(1, customerID);
			rs = updateCustomerStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempCustomer = new Customer();
				tempCustomer.setPersonID(convertIDToString(rs.getInt("customerID")));
				tempCustomer.setPassportNumber(rs.getString("passportNumber"));
				tempCustomer.setNationality(rs.getString("nationality"));
			}
			//update the employee data second
			if (tempCustomer != null && rs.getInt("customerID") == customerID) {
				rs.updateString("passportNumber", customer.getPassportNumber());
				rs.updateString("nationality", customer.getNationality());
				rs.updateRow();
				result = true;
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=updatePersonStatement)updatePersonStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=updateCustomerStatement)updateCustomerStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public String createJourney(Journey journey)
	{
		PreparedStatement createJourneyStatement = null;
		PreparedStatement idQueryStatement = null;
		String result = null;
		int rowcount;
		
		String secondFlightIDString = journey.getSecondFlightID();
		int secondFlightID = 0;
		if(secondFlightIDString != null && secondFlightIDString.equals("") == false){
			secondFlightID = Integer.parseInt(secondFlightIDString);
		}
		try {
			String query = "Insert into journey (customerID, boardingPoint, destination, " +
					"paymentOption, firstFlightID, firstFlightNumber, secondFlightID, " +
					"secondFlightNumber, totalPrice, intermediatePoint, airline, firstFlightStart, " +
					"firstFlightEnd, secondFlightStart, secondFlightEnd) values " +
					"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			createJourneyStatement = con.prepareStatement(query);
			createJourneyStatement.setInt(1, convertIDToInteger(journey.getCustomerID()));
			createJourneyStatement.setString(2, journey.getBoardingPoint());
			createJourneyStatement.setString(3, journey.getDestination());
			createJourneyStatement.setString(4, journey.getPaymentOption());
			createJourneyStatement.setInt(5, Integer.parseInt(journey.getFirstFlightID()));
			createJourneyStatement.setString(6, journey.getFirstFlightNumber());
			createJourneyStatement.setInt(7, secondFlightID);
			createJourneyStatement.setString(8, journey.getSecondFlightNumber());
			createJourneyStatement.setString(9, journey.getTotalPrice());
			createJourneyStatement.setString(10, journey.getIntermediatePoint());
			createJourneyStatement.setString(11, journey.getAirline());
			createJourneyStatement.setString(12, journey.getFirstFlightStart());
			createJourneyStatement.setString(13, journey.getFirstFlightEnd());
			createJourneyStatement.setString(14, journey.getSecondFlightStart());
			createJourneyStatement.setString(15, journey.getSecondFlightEnd());
			rowcount=createJourneyStatement.executeUpdate();
			if(rowcount > 0){
				idQueryStatement = con.prepareStatement("Select last_insert_id()");
				rs = idQueryStatement.executeQuery();
//				String idQuery = "Select last_insert_id()";
//				rs=stmt.executeQuery(idQuery);
				if(rs.next() != false){
					result = Integer.toString(rs.getInt(1));
				}
			}
			else{
				result=null;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=createJourneyStatement)createJourneyStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=idQueryStatement)idQueryStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public Boolean deleteJourney(int journeyID)
	{
		PreparedStatement deleteJourneyStatement = null;
		Boolean result = false;
		int rowcount;
		try {
			String query = "Delete from journey where journeyID = ?";
			deleteJourneyStatement = con.prepareStatement(query);
			deleteJourneyStatement.setInt(1, journeyID);
			rowcount=deleteJourneyStatement.executeUpdate(query);
			if(rowcount > 0){
				return true;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=deleteJourneyStatement)deleteJourneyStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
			cp.checkin(con);
		}
		return result;
	}
	
	public Journey getJourneyByID(int journeyID)
	{
		PreparedStatement queryJourneyStatement = null;
		Journey tempJourney = null;
		try {
			String query = "Select * from journey where journeyID = ?";
	        queryJourneyStatement = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			queryJourneyStatement.setInt(1, journeyID);
	        rs = queryJourneyStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the Journey object
				tempJourney = new Journey();
				tempJourney.setJourneyID(Integer.toString(rs.getInt("journeyID")));
				tempJourney.setCustomerID(convertIDToString(rs.getInt("customerID")));
				tempJourney.setBoardingPoint(rs.getString("boardingPoint"));
				tempJourney.setDestination(rs.getString("destination"));
				tempJourney.setDestination(rs.getString("destination"));
				tempJourney.setPaymentOption(rs.getString("paymentOption"));
				tempJourney.setFirstFlightID(Integer.toString(rs.getInt("firstFlightID")));
				tempJourney.setFirstFlightNumber(rs.getString("firstFlightNumber"));
				tempJourney.setSecondFlightID(Integer.toString(rs.getInt("secondFlightID")));
				tempJourney.setSecondFlightNumber(rs.getString("secondFlightNumber"));
				tempJourney.setTotalPrice(rs.getString("totalPrice"));
				tempJourney.setIntermediatePoint(rs.getString("intermediatePoint"));
				tempJourney.setAirline(rs.getString("airline"));
				tempJourney.setFirstFlightStart(rs.getString("firstFlightStart"));
				tempJourney.setFirstFlightEnd(rs.getString("firstFlightEnd"));
				tempJourney.setSecondFlightEnd(rs.getString("secondFlightEnd"));
				tempJourney.setSecondFlightStart(rs.getString("secondFlightStart"));
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=queryJourneyStatement)queryJourneyStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return tempJourney;
	}
	
	public String createFlight(Flight flight)
	{
		PreparedStatement createFlightStatement = null;
		PreparedStatement idQueryStatement = null;
		String result = null;
		int rowcount;
		try {
			String query = "Insert into flight (flightNumber, airlineName, source, " +
					"destination, boardTime, destTime, totalSeatCount, availableSeatCount, " +
					"crewDetails, flightStatus, price) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			createFlightStatement = con.prepareStatement(query);
			createFlightStatement.setString(1, flight.getFlightNumber());
			createFlightStatement.setString(2, flight.getAirlineName());
			createFlightStatement.setString(3, flight.getSource());
			createFlightStatement.setString(4, flight.getDestination());
			createFlightStatement.setString(5, flight.getBoardTime());
			createFlightStatement.setString(6, flight.getDestTime());
			createFlightStatement.setString(7, flight.getTotalSeatCount());
			createFlightStatement.setString(8, flight.getAvailableSeatCount());
			createFlightStatement.setString(9, flight.getCrewDetails());
			createFlightStatement.setString(10, flight.getFlightStatus());
			createFlightStatement.setString(11, flight.getPrice());
			rowcount=createFlightStatement.executeUpdate();
			if(rowcount > 0){
				idQueryStatement = con.prepareStatement("Select last_insert_id()");
				rs = idQueryStatement.executeQuery();
//				String idQuery = "Select last_insert_id()";
//				rs=stmt.executeQuery(idQuery);
				if(rs.next() != false){
					result = Integer.toString(rs.getInt(1));
				}
			}
			else{
				result=null;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=createFlightStatement)createFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=idQueryStatement)idQueryStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public Boolean deleteFlight(int flightID)
	{
		PreparedStatement deleteFlightStatement = null;
		Boolean result = false;
		int rowcount;
		try {
			String query = "Delete from flight where flightID = ?";
			deleteFlightStatement = con.prepareStatement(query);
			deleteFlightStatement.setInt(1, flightID);
			rowcount=deleteFlightStatement.executeUpdate();
			if(rowcount > 0){
				return true;
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=deleteFlightStatement)deleteFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public Boolean updateFlight(Flight flight)
	{
		PreparedStatement updateFlightStatement = null;
		Boolean result = false;
		int flightID = Integer.parseInt(flight.getFlightID());
		Flight tempFlight = null;
		
		try {
			String query = "Select * from flight where flightID = ?";
			updateFlightStatement = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			updateFlightStatement.setInt(1, flightID);
			rs = updateFlightStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempFlight = new Flight();
				tempFlight.setFlightID(Integer.toString(rs.getInt("flightID")));
				tempFlight.setFlightNumber(rs.getString("flightNumber"));
				tempFlight.setAirlineName(rs.getString("airlineName"));
				tempFlight.setSource(rs.getString("source"));
				tempFlight.setDestination(rs.getString("destination"));
				tempFlight.setBoardTime(rs.getString("boardTime"));
				tempFlight.setDestTime(rs.getString("destTime"));
				tempFlight.setTotalSeatCount(Integer.toString(rs.getInt("totalSeatCount")));
				tempFlight.setAvailableSeatCount(Integer.toString(rs.getInt("availableSeatCount")));
				tempFlight.setCrewDetails(rs.getString("crewDetails"));
				tempFlight.setFlightStatus(rs.getString("flightStatus"));
				tempFlight.setPrice(rs.getString("price"));
			}
			//update the flight data 
			//TODO: need to do the checks if the data changed before it will be updated, right now just does all
			//Flight tempFlight = getFlightByID(flightID);			
			if (tempFlight != null && rs.getInt("flightID") == flightID) {
				rs.updateInt("totalSeatCount", Integer.parseInt(flight.getTotalSeatCount()));
				rs.updateInt("availableSeatCount", Integer.parseInt(flight.getAvailableSeatCount()));
				rs.updateString("crewDetails", flight.getCrewDetails());
				rs.updateString("price", flight.getPrice());
				rs.updateString("flightStatus", flight.getFlightStatus());
				rs.updateRow();
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=updateFlightStatement)updateFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return result;
	}
	
	public Flight getFlightByID(int flightID)
	{
		PreparedStatement queryFlightStatement = null;
		Flight tempFlight = null;
		try {
			String query = "Select * from flight where flightID = ?";
	        queryFlightStatement = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	        queryFlightStatement.setInt(1, flightID);
			rs = queryFlightStatement.executeQuery();
			if(rs.next() != false) {
				//fill in the person object
				tempFlight = new Flight();
				tempFlight.setFlightID(Integer.toString(rs.getInt("flightID")));
				tempFlight.setFlightNumber(rs.getString("flightNumber"));
				tempFlight.setAirlineName(rs.getString("airlineName"));
				tempFlight.setSource(rs.getString("source"));
				tempFlight.setDestination(rs.getString("destination"));
				tempFlight.setBoardTime(rs.getString("boardTime"));
				tempFlight.setDestTime(rs.getString("destTime"));
				tempFlight.setTotalSeatCount(Integer.toString(rs.getInt("totalSeatCount")));
				tempFlight.setAvailableSeatCount(Integer.toString(rs.getInt("availableSeatCount")));
				tempFlight.setCrewDetails(rs.getString("crewDetails"));
				tempFlight.setFlightStatus(rs.getString("flightStatus"));
				tempFlight.setPrice(rs.getString("price"));
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=queryFlightStatement)queryFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return tempFlight;
	}
	
	public Employee[] searchEmployees(Employee employee, int startingPoint, int numOfRecords)
	{
		Employee[] employees = null;
		PreparedStatement allEmployeeSearch = null;
		Employee tempEmployee = new Employee();
		try {;
			
			if(employee != null) {
				stmt = con.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
				String query = "Select person.personID, person.password, person.firstName, person.lastName, " +
						"person.address, person.city, person.state, person.zipCode, person.dateOfBirth, employee.workDescription, " +
						"employee.position, employee.hireDate from person, employee where person.personID = employee.employeeID";
				//add to the query when the Employee parameter has data filled in
				if (!employee.getPassword().equals("")) {
					query += " and person.password = '" + employee.getPassword() + "'";
				}
				if (!employee.getFirstName().equals("")) {
					query += " and person.firstName = '" + employee.getFirstName() + "'";
				}
				if (!employee.getLastName().equals("")) {
					query += " and person.lastName = '" + employee.getLastName() + "'";
				}
				if (!employee.getAddress().equals("")) {
					query += " and person.address = '" + employee.getAddress() + "'";
				}
				if (!employee.getCity().equals("")) {
					query += " and person.city = '" + employee.getCity() + "'";
				}
				if (!employee.getState().equals("")) {
					query += " and person.state = '" + employee.getState() + "'";
				}
				if (!employee.getZipCode().equals("")) {
					query += " and person.zipCode = '" + employee.getZipCode() + "'";
				}
				if (!employee.getDateOfBirth().equals("")) {
					query += " and person.dateOfBirth = '" + employee.getDateOfBirth() + "'"; 
				}
				if (!employee.getWorkDescription().equals("")) {
					query += " and employee.workDescription = '" + employee.getWorkDescription() + "'";
				}
				if (!employee.getPosition().equals("")) {
					query += " and employee.position = '" + employee.getPosition() + "'";
				}
				if (!employee.getHireDate().equals("")) {
					query += " and employee.hireDate = '" + employee.getHireDate() + "'";
				}
				query += " limit " + startingPoint + "," + numOfRecords;
				rs = stmt.executeQuery(query);
			} else {
				//passed in Customer was null, so just return all
				String allQuery = "Select person.personID, person.password, person.firstName, person.lastName, " +
						"person.address, person.city, person.state, person.zipCode, person.dateOfBirth, employee.workDescription, " +
						"employee.position, employee.hireDate from person, employee where person.personID = employee.employeeID " +
						"limit ?, ?";
				allEmployeeSearch = con.prepareStatement(allQuery,ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
				allEmployeeSearch.setInt(1, startingPoint);
				allEmployeeSearch.setInt(2, numOfRecords);
				rs = allEmployeeSearch.executeQuery();
			}
			 
			rs.last();
			int numRows = rs.getRow();
			rs.first();
			employees = new Employee[numRows];
			for (int i = 0; i < numRows; i++) {
				//fill in the person object
				tempEmployee = new Employee();
				tempEmployee.setPersonID(convertIDToString(rs.getInt("person.personID")));
				tempEmployee.setPassword(rs.getString("person.password"));
				tempEmployee.setFirstName(rs.getString("person.firstName"));
				tempEmployee.setLastName(rs.getString("person.lastName"));
				tempEmployee.setAddress(rs.getString("person.address"));
				tempEmployee.setCity(rs.getString("person.city"));
				tempEmployee.setState(rs.getString("person.state"));
				tempEmployee.setZipCode(rs.getString("person.zipCode"));
				tempEmployee.setDateOfBirth(rs.getString("person.dateOfBirth"));
				tempEmployee.setWorkDescription(rs.getString("employee.workDescription"));
				tempEmployee.setPosition(rs.getString("employee.position"));
				tempEmployee.setHireDate(rs.getString("employee.hireDate"));
				employees[i] = tempEmployee;
				rs.next();
			}
			
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=allEmployeeSearch)allEmployeeSearch.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return employees;
	}
	
	public Customer[] searchCustomers(Customer customer, int startingPoint, int numOfRecords)
	{
		Customer[] customers = null;
		Customer tempCustomer = new Customer();
		PreparedStatement allCustomerSearch = null;
		try {

			if(customer != null) {
		        stmt = con.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
				String query = "Select person.personID, person.password, person.firstName, person.lastName, " +
						"person.address, person.city, person.state, person.zipCode, person.dateOfBirth, customer.passportNumber, " +
						"customer.nationality from person, customer where person.personID = customer.customerID";
				//add to the query when the Customer parameter has data filled in
				if (!customer.getPassword().equals("")) {
					query += " and person.password = '" + customer.getPassword() + "'";
				}
				if (!customer.getFirstName().equals("")) {
					query += " and person.firstName = '" + customer.getFirstName() + "'";
				}
				if (!customer.getLastName().equals("")) {
					query += " and person.lastName = '" + customer.getLastName() + "'";
				}
				if (!customer.getAddress().equals("")) {
					query += " and person.address = '" + customer.getAddress() + "'";
				}
				if (!customer.getCity().equals("")) {
					query += " and person.city = '" + customer.getCity() + "'";
				}
				if (!customer.getState().equals("")) {
					query += " and person.state = '" + customer.getState() + "'";
				}
				if (!customer.getZipCode().equals("")) {
					query += " and person.zipCode = '" + customer.getZipCode() + "'";
				}
				if (!customer.getDateOfBirth().equals("")) {
					query += " and person.dateOfBirth = '" + customer.getDateOfBirth() + "'"; 
				}
				if (!customer.getPassportNumber().equals("")) {
					query += " and customer.passportNumber = '" + customer.getPassportNumber() + "'";
				}
				if (!customer.getNationality().equals("")) {
					query += " and customer.nationality = '" + customer.getNationality() + "'";
				}
				query += " limit " + startingPoint + "," + numOfRecords;
				rs = stmt.executeQuery(query);
			} else {
				//passed in Customer was null, so just return all
				String allQuery = "Select person.personID, person.password, person.firstName, person.lastName, " +
						"person.address, person.city, person.state, person.zipCode, person.dateOfBirth, customer.passportNumber, " +
						"customer.nationality from person, customer where person.personID = customer.customerID " +
						"limit ?, ?";
				
				allCustomerSearch = con.prepareStatement(allQuery,ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
				allCustomerSearch.setInt(1, startingPoint);
				allCustomerSearch.setInt(2, numOfRecords);
				rs = allCustomerSearch.executeQuery();
			}
			rs.last();
			int numRows = rs.getRow();
			rs.first();
			customers = new Customer[numRows];
			for (int i = 0; i < numRows; i++) {
				//fill in the person object
				tempCustomer = new Customer();
				tempCustomer.setPersonID(convertIDToString(rs.getInt("person.personID")));
				tempCustomer.setPassword(rs.getString("person.password"));
				tempCustomer.setFirstName(rs.getString("person.firstName"));
				tempCustomer.setLastName(rs.getString("person.lastName"));
				tempCustomer.setAddress(rs.getString("person.address"));
				tempCustomer.setCity(rs.getString("person.city"));
				tempCustomer.setState(rs.getString("person.state"));
				tempCustomer.setZipCode(rs.getString("person.zipCode"));
				tempCustomer.setDateOfBirth(rs.getString("person.dateOfBirth"));
				tempCustomer.setPassportNumber(rs.getString("customer.passportNumber"));
				tempCustomer.setNationality(rs.getString("customer.nationality"));
				customers[i] = tempCustomer;
				rs.next();
			}

		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=allCustomerSearch)allCustomerSearch.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return customers;
	}
	
	public Journey[] searchJourneys(Journey journey) 
	{
		Journey[] journeys = null;
		ArrayList<Journey> journeyList = new ArrayList<Journey>();
		Journey tempJourney = new Journey();
		try {
			stmt = con.createStatement();
			stmt = con.createStatement();
	        stmt = con.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
			String query = "Select * from journey";
			
			if(journey != null){
				Boolean firstParameter = true;
				//add to the query when the Journey parameter has data filled in
				if (!journey.getCustomerID().equals("")) {
					query += " where customerID = " + String.format("%09d", convertIDToInteger(journey.getCustomerID()));
					firstParameter = false;
				}
				if (!journey.getBoardingPoint().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "boardingPoint = '" + journey.getBoardingPoint() + "'";
					firstParameter = false;
				}
				if (!journey.getDestination().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "destination = '" + journey.getDestination() + "'";
					firstParameter = false;
				}
				if (!journey.getPaymentOption().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "paymentOption = '" + journey.getPaymentOption() + "'";
					firstParameter = false;
				}
				if (!journey.getFirstFlightID().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "firstFlightID = " + Integer.parseInt(journey.getFirstFlightID());
					firstParameter = false;
				}
				if (!journey.getFirstFlightNumber().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "firstFlightNumber = '" + journey.getFirstFlightNumber() + "'";
					firstParameter = false;
				}
				if (!journey.getSecondFlightID().equals("") && !journey.getSecondFlightID().equals("0")) {
					query += (firstParameter) ? " where " : " and ";
					query += "secondFlightID = " + Integer.parseInt(journey.getSecondFlightID());
					firstParameter = false;
				}
				if (!journey.getSecondFlightNumber().equals("") && !journey.getSecondFlightNumber().equals("0")) {
					query += (firstParameter) ? " where " : " and ";
					query += "secondFlightNumber = '" + journey.getSecondFlightNumber() + "'";
					firstParameter = false;
				}
				if (!journey.getTotalPrice().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "totalPrice = '" + journey.getTotalPrice() + "'";
					firstParameter = false;
				}
				if (!journey.getIntermediatePoint().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "intermediatePoint = '" + journey.getIntermediatePoint() + "'";
					firstParameter = false;
				}
				if (!journey.getAirline().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "airline = '" + journey.getAirline() + "'";
					firstParameter = false;
				}
				if (!journey.getFirstFlightStart().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "firstFlightStart = '" + journey.getFirstFlightStart() + "'";
					firstParameter = false;
				}
				if (!journey.getFirstFlightEnd().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "firstFlightEnd = '" + journey.getFirstFlightEnd() + "'";
					firstParameter = false;
				}
				if (!journey.getSecondFlightStart().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "secondFlightStart = '" + journey.getSecondFlightStart() + "'";
					firstParameter = false;
				}
				if (!journey.getSecondFlightEnd().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "secondFlightEnd = '" + journey.getSecondFlightEnd() + "'";
					firstParameter = false;
				}
			}
					
			ResultSet rs = stmt.executeQuery(query);
			rs.last();
			int numRows = rs.getRow();
			rs.first();
			for (int i = 0; i < numRows; i++) {
				//fill in the person object
				tempJourney = new Journey();
				tempJourney.setJourneyID(Integer.toString(rs.getInt("journeyID")));
				tempJourney.setCustomerID(convertIDToString(rs.getInt("customerID")));
				tempJourney.setBoardingPoint(rs.getString("boardingPoint"));
				tempJourney.setDestination(rs.getString("destination"));
				tempJourney.setPaymentOption(rs.getString("paymentOption"));
				tempJourney.setFirstFlightID(Integer.toString(rs.getInt("firstFlightID")));
				tempJourney.setFirstFlightNumber(rs.getString("firstFlightNumber"));
				tempJourney.setSecondFlightID(Integer.toString(rs.getInt("secondFlightID")));
				tempJourney.setSecondFlightNumber(rs.getString("secondFlightNumber"));
				tempJourney.setTotalPrice(rs.getString("totalPrice"));
				tempJourney.setIntermediatePoint(rs.getString("intermediatePoint"));
				tempJourney.setAirline(rs.getString("airline"));
				tempJourney.setFirstFlightStart(rs.getString("firstFlightStart"));
				tempJourney.setFirstFlightEnd(rs.getString("firstFlightEnd"));
				tempJourney.setSecondFlightEnd(rs.getString("secondFlightEnd"));
				tempJourney.setSecondFlightStart(rs.getString("secondFlightStart"));
				journeyList.add(tempJourney);
				rs.next();
			}
			
			if (journeyList.size() > 0) {
				Object[] objects = journeyList.toArray();
				journeys = new Journey[journeyList.size()];
				
				for (int i = 0; i < objects.length; i++) {
					journeys[i] = (Journey) objects[i];
				}
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return journeys;
	}
	
	public Flight[] searchFlights(Flight flight, int startingPoint, int numOfRecords)
	{
		Flight[] flights = null;
		ArrayList<Flight> flightList = new ArrayList<Flight>();
		Flight tempFlight = new Flight();
		try {
	        stmt = con.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
			String query = "Select * from flight";
			
			if (flight != null) {
				Boolean firstParameter = true;
				//add to the query when the Employee parameter has data filled in
				if (!flight.getFlightNumber().equals("")) {
					query += " where flightNumber = '" + flight.getFlightNumber() + "'";
					firstParameter = false;
				}
				if (!flight.getAirlineName().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "airlineName = '" + flight.getAirlineName() + "'";
					firstParameter = false;
				}
				if (!flight.getSource().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "source = '" + flight.getSource() + "'";
					firstParameter = false;
				}
				if (!flight.getDestination().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "destination = '" + flight.getDestination() + "'";
					firstParameter = false;
				}
				if (!flight.getBoardTime().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "boardTime = '" + flight.getBoardTime() + "'";
					firstParameter = false;
				}
				if (!flight.getDestTime().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "destTime = '" + flight.getDestTime() + "'";
					firstParameter = false;
				}
				if (!flight.getTotalSeatCount().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "totalSeatCount = " + Integer.parseInt(flight.getTotalSeatCount());
					firstParameter = false;
				}
				if (!flight.getAvailableSeatCount().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "availableSeatCount = " + Integer.parseInt(flight.getAvailableSeatCount());
					firstParameter = false;
				}
				if (!flight.getCrewDetails().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "crewDetails = '" + flight.getCrewDetails() + "'";
					firstParameter = false;
				}
				if (!flight.getFlightStatus().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "flightStatus = '" + flight.getFlightStatus() + "'";
					firstParameter = false;
				}
				if (!flight.getPrice().equals("")) {
					query += (firstParameter) ? " where " : " and ";
					query += "price = '" + flight.getPrice() + "'";
					firstParameter = false;
				}
			}
			query += " limit " + startingPoint + "," + numOfRecords;
					
			ResultSet rs = stmt.executeQuery(query);
			rs.last();
			int numRows = rs.getRow();
			rs.first();
			for (int i = 0; i < numRows; i++) {
				//fill in the person object
				tempFlight = new Flight();
				tempFlight.setFlightID(Integer.toString(rs.getInt("flightID")));
				tempFlight.setFlightNumber(rs.getString("flightNumber"));
				tempFlight.setAirlineName(rs.getString("airlineName"));
				tempFlight.setSource(rs.getString("source"));
				tempFlight.setDestination(rs.getString("destination"));
				tempFlight.setBoardTime(rs.getString("boardTime"));
				tempFlight.setDestTime(rs.getString("destTime"));
				tempFlight.setTotalSeatCount(Integer.toString(rs.getInt("totalSeatCount")));
				tempFlight.setAvailableSeatCount(Integer.toString(rs.getInt("availableSeatCount")));
				tempFlight.setCrewDetails(rs.getString("crewDetails"));
				tempFlight.setFlightStatus(rs.getString("flightStatus"));
				tempFlight.setPrice(rs.getString("price"));
				flightList.add(tempFlight);
				rs.next();
			}
			
			if (flightList.size() > 0) {
				Object[] objects = flightList.toArray();
				flights = new Flight[flightList.size()];
				
				for (int i = 0; i < objects.length; i++) {
					flights[i] = (Flight) objects[i];
				}
			}
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return flights;
	}
	
	public Journey[] searchItineraries(Flight flight, int startingPoint, int numOfRecords) 
	{
		Journey[] journeys = null;
		HashMap<Double, Journey> allJourneys = new HashMap<Double, Journey>();
		allJourneys.putAll(searchNonStopItineraries(flight, 0, 50));
		allJourneys.putAll(searchOneStopFlights(flight, 0, 50));
		
		//finally, just return the cheapest first 50;
		Object[] sortedKeys = allJourneys.keySet().toArray();
		Arrays.sort(sortedKeys);
		
		int total = (sortedKeys.length < numOfRecords) ? sortedKeys.length : 50;
		
		journeys = new Journey[total];
		for (int i = 0; i < total; i++) {
			journeys[i] = allJourneys.get(sortedKeys[i]);
		}
		return journeys;
		
	}
	
	private HashMap<Double, Journey> searchNonStopItineraries(Flight flight, int startingPoint, int numOfRecords)
	{
		HashMap<Double, Journey> foundItineraries = new HashMap<Double, Journey>();
		Journey tempJourney = new Journey();
		PreparedStatement nonStopStatement = null;
		try {
			String query = "Select * from flight where availableSeatCount > 0"
					+ " and airlineName = ? and source = ? and destination = ? order by price asc "
					+ "limit ?, ?";
	        nonStopStatement = con.prepareStatement(query, ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
			nonStopStatement.setString(1, flight.getAirlineName());
			nonStopStatement.setString(2, flight.getSource());
			nonStopStatement.setString(3, flight.getDestination());
			nonStopStatement.setInt(4, startingPoint);
			nonStopStatement.setInt(5, numOfRecords);
					
			rs = nonStopStatement.executeQuery();
			rs.last();
			int numRows = rs.getRow();
			rs.first();
			//nonstops = new Journey[numRows];
			for (int i = 0; i < numRows; i++) {
				//fill in the journey object
				tempJourney = new Journey();
				tempJourney.setBoardingPoint(rs.getString("source"));
				tempJourney.setDestination(rs.getString("destination"));
				tempJourney.setFirstFlightID(Integer.toString(rs.getInt("flightID")));
				tempJourney.setFirstFlightNumber(rs.getString("flightNumber"));
				tempJourney.setTotalPrice(rs.getString("price"));
				tempJourney.setAirline(rs.getString("airlineName"));
				tempJourney.setFirstFlightStart(rs.getString("boardTime"));
				tempJourney.setFirstFlightEnd(rs.getString("destTime"));
				tempJourney.setSecondFlightEnd("");
				tempJourney.setSecondFlightStart("");
				tempJourney.setIntermediatePoint("");
				tempJourney.setSecondFlightID("0");
				tempJourney.setSecondFlightNumber("0");
				tempJourney.setIntermediatePoint("");
				foundItineraries.put(Double.parseDouble(rs.getString("price")), tempJourney);
				rs.next();
			}

		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=nonStopStatement)nonStopStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return foundItineraries;
	}
	
	private HashMap<Double, Journey> searchOneStopFlights(Flight flight, int startingPoint, int numOfRecords)
	{
		HashMap<Double, Journey> foundItineraries = new HashMap<Double, Journey>();
		PreparedStatement firstFlightStatement = null;
		PreparedStatement secondFlightStatement = null;
		Journey tempJourney = null;
		try {
			String query = "Select * from flight where availableSeatCount > 0 and airlineName = ?" +
					" and source = ? and destination <> ? order by price, boardTime asc limit ?, ?";
	        firstFlightStatement = con.prepareStatement(query, ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
			firstFlightStatement.setString(1, flight.getAirlineName());
			firstFlightStatement.setString(2, flight.getSource());
			firstFlightStatement.setString(3, flight.getDestination());
			firstFlightStatement.setInt(4, startingPoint);
			firstFlightStatement.setInt(5, numOfRecords);
					
			rs = firstFlightStatement.executeQuery();
			while (rs.next()) {
				//for each flight returned, get second flights that work for it
				String secondFlightQuery = "Select * from flight where availableSeatCount > 0 and airlineName = ?" +
						" and source = ? and destination = ? and boardTime > ? order by price, boardTime" +
						" asc limit 0, 5";
		        secondFlightStatement = con.prepareStatement(secondFlightQuery, ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
				secondFlightStatement.setString(1, flight.getAirlineName());
				secondFlightStatement.setString(2, rs.getString("destination"));
				secondFlightStatement.setString(3, flight.getDestination());
				secondFlightStatement.setString(4, rs.getString("destTime"));
						
				rs2 = secondFlightStatement.executeQuery();
				while (rs2.next()) {
					tempJourney = new Journey();
					tempJourney.setBoardingPoint(flight.getSource());
					tempJourney.setDestination(flight.getDestination());
					tempJourney.setIntermediatePoint(rs.getString("destination"));
					tempJourney.setFirstFlightID(Integer.toString(rs.getInt("flightID")));
					tempJourney.setFirstFlightNumber(rs.getString("flightNumber"));
					tempJourney.setSecondFlightID(Integer.toString(rs2.getInt("flightID")));
					tempJourney.setSecondFlightNumber(rs2.getString("flightNumber"));
					tempJourney.setAirline(rs.getString("airlineName"));
					tempJourney.setFirstFlightStart(rs.getString("boardTime"));
					tempJourney.setFirstFlightEnd(rs.getString("destTime"));
					tempJourney.setSecondFlightEnd(rs2.getString("destTime"));
					tempJourney.setSecondFlightStart(rs2.getString("boardTime"));
					
					//calculate price
					double firstLegPrice = Double.parseDouble(rs.getString("price"));
					double secondLegPrice = Double.parseDouble(rs2.getString("price"));
					double totalPrice = firstLegPrice + secondLegPrice;
					DecimalFormat df = new DecimalFormat("0.00");
					String result = df.format(totalPrice);
					tempJourney.setTotalPrice(result);
					foundItineraries.put(totalPrice, tempJourney);
				}
				rs.next();
			}
			
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=rs2)rs2.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=firstFlightStatement)firstFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        try { if(null!=secondFlightStatement)secondFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return foundItineraries;
	}
	
	public Flight[] searchSecondLegFlights(Flight flight, int startingPoint, int numOfRecords)
	{
		Flight[] destFlights = null;
		Flight tempFlight = new Flight();
		PreparedStatement secondFlightStatement = null;
		
		try {
			String query = "Select * from flight where availableSeatCount > 0 and airlineName = ?" +
					" and source = ? and destination = ? and boardTime > ? order by price, boardTime" +
					" asc limit ?, ?";
	        secondFlightStatement = con.prepareStatement(query, ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
			secondFlightStatement.setString(1, flight.getAirlineName());
			secondFlightStatement.setString(2, flight.getSource());
			secondFlightStatement.setString(3, flight.getDestination());
			secondFlightStatement.setString(4, flight.getDestTime());
			secondFlightStatement.setInt(5, startingPoint);
			secondFlightStatement.setInt(6, numOfRecords);
					
			ResultSet rs = secondFlightStatement.executeQuery();
			rs.last();
			int numRows = rs.getRow();
			rs.first();
			destFlights = new Flight[numRows];
			for (int i = 0; i < numRows; i++) {
				//fill in the person object
				tempFlight = new Flight();
				tempFlight.setFlightID(Integer.toString(rs.getInt("flightID")));
				tempFlight.setFlightNumber(rs.getString("flightNumber"));
				tempFlight.setAirlineName(rs.getString("airlineName"));
				tempFlight.setSource(rs.getString("source"));
				tempFlight.setDestination(rs.getString("destination"));
				tempFlight.setBoardTime(rs.getString("boardTime"));
				tempFlight.setDestTime(rs.getString("destTime"));
				tempFlight.setTotalSeatCount(Integer.toString(rs.getInt("totalSeatCount")));
				tempFlight.setAvailableSeatCount(Integer.toString(rs.getInt("availableSeatCount")));
				tempFlight.setCrewDetails(rs.getString("crewDetails"));
				tempFlight.setFlightStatus(rs.getString("flightStatus"));
				tempFlight.setPrice(rs.getString("price"));
				destFlights[i] = tempFlight;
				rs.next();
			}		
		} catch (SQLException e) {
			//TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
	        try { if(null!=rs)rs.close();} catch (SQLException e)
	        {e.printStackTrace();}
//	        try { if(null!=stmt)stmt.close();} catch (SQLException e)
//	        {e.printStackTrace();}
	        try { if(null!=secondFlightStatement)secondFlightStatement.close();} catch (SQLException e)
	        {e.printStackTrace();}
	        cp.checkin(con);
			}
		return destFlights;
	}
	// HELPER METHODS:
	
	private int convertIDToInteger(String id) 
	{
		String noHyphenString = id.replaceAll("-", "");
		return Integer.parseInt(noHyphenString);		
	}
	
	private String convertIDToString(int id)
	{
		//String integerString = Integer.toString(id);
		String integerString = String.format("%09d", id);
		String firstThree = integerString.substring(0, 3);
		String nextTwo = integerString.substring(3, 5);
		String lastFour = integerString.substring(5);
		return firstThree + "-" + nextTwo + "-" + lastFour;
	}
}
